SQL LIKE Clause

    With SQL LIKE clause, we can match a specific pattern from a data value, we mostly use LIKE clause for search purpose. With LIKE operator we use two wildcards, and these two wildcards are used to create search patterns:

    1. % (Percentage)- This symbol represents zero or more than zero characters.
    2. _ (Underscore) - This symbol represents a single character.

    LIKE operator Syntax

    To use LIKE operator, follow this syntax: 

    SELECT column_name_1, column_name_2, ...
FROM table_name
WHERE column_name LIKE search_pattern;

    Syntax Example With the help of two wildcards of LIKE operators, we can generate different search patterns.

    Wildcard Pattern Description
    % 
    'ai%'
    		 Look for that data which value starts with “ ai ”
    % 
    '%ai%'
    		 Look for that data which have ai in it
    _ % 
    '_ai%'
    		 Look for that data which second value is a and third value is i.
    _ % _ % 
    'a_%_%'
    		 Look for that data which value starts with a and have at least 3 characters.
    % 
    '%a'
    		 Look for that data which value ends with a
    _ % 
    '_a%i'
    		 Look for that data which 2 nd value is a and ends with i
    _ 
    'a___i'
    		 Look for a value which total length is 5 and its stating value is a and ending value is i

    SQL Like Example

    For the example considered this Students table: 

    +------+--------+------+--------+-------+
| id   | name   | age  | grades | marks |
+------+--------+------+--------+-------+
|    1 | Luffy  |   16 | A      |   970 |
|    2 | Naruto |   18 | A      |   960 |
|    3 | Zoro   |   20 | A      |   940 |
|    4 | Sanji  |   21 | B      |   899 |
|    5 | Nami   |   17 | B      |   896 |
|    6 | Robin  | NULL | B      |   860 |
+------+--------+------+--------+-------+

    Query: Display those students records whose name starts with N 

    SELECT *
FROM students
WHERE name LIKE 'N%';

    Output 

    +------+--------+------+--------+-------+
| id   | name   | age  | grades | marks |
+------+--------+------+--------+-------+
|    2 | Naruto |   18 | A      |   960 |
|    5 | Nami   |   17 | B      |   896 |
+------+--------+------+--------+-------+

    Query: Display those students records whose name ends with i: 

    SELECT *
FROM students
WHERE name LIKE '%i';

    Output 

    +------+-------+------+--------+-------+
| id   | name  | age  | grades | marks |
+------+-------+------+--------+-------+
|    4 | Sanji |   21 | B      |   899 |
|    5 | Nami  |   17 | B      |   896 |
+------+-------+------+--------+-------+

    Query: Display those students records whose has the letter r in their name 

    SELECT *
FROM students
WHERE name LIKE '%r%';

    Output 

    +------+--------+------+--------+-------+
| id   | name   | age  | grades | marks |
+------+--------+------+--------+-------+
|    2 | Naruto |   18 | A      |   960 |
|    3 | Zoro   |   20 | A      |   940 |
|    6 | Robin  | NULL | B      |   860 |
+------+--------+------+--------+-------+

    Query: Display those students records whose name second letter is a 

    SELECT *
FROM students
WHERE name LIKE '_a%';

    Output 

    +------+--------+------+--------+-------+
| id   | name   | age  | grades | marks |
+------+--------+------+--------+-------+
|    2 | Naruto |   18 | A      |   960 |
|    4 | Sanji  |   21 | B      |   899 |
|    5 | Nami   |   17 | B      |   896 |
+------+--------+------+--------+-------+

    Query: Display those students records whose name start with N and their name length must be greater than 4. 

    SELECT *
FROM students
WHERE name LIKE 'n____%';

    Output 

    +------+--------+------+--------+-------+
| id   | name   | age  | grades | marks |
+------+--------+------+--------+-------+
|    2 | Naruto |   18 | A      |   960 |
+------+--------+------+--------+-------+

    Summary

    • LIKE operator is used to search for the matching pattern from the data set.
    • To create a pattern we have two LIKE wildcards %(percentage) and _(Underscore)
    • % represents zero or more than zero characters
    • _ represent a single character.
    • We always use LIKE operator with the WHERE clause.

    People are also reading:

    Previous Tutorial Next Tutorial